package db.queries.core;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import core.exceptions.DBAccessException;
import core.objects.ObjectWithID;
import db.DBHandler;
import db.ICrossInsert;
import db.dbTables.AbstractCrossDBTable;
import db.dbTables.AbstractDBTable;
import db.dbTables.DbColumn;

public abstract class BaseObjectQueriesInfo<T extends ObjectWithID> extends BaseObjectQueries<T> {

	/**
	 * determines which table the query in question needs to operate on
	 *
	 * @param obj the object by which we determine
	 * @param selectObj are we selecting by this object or not (for instance,
	 *                  if Category is supplied for Book but selectObj is
	 *                  false, it means "select category by book", however
	 *                  if selectObj is true, then we want to select book
	 *                  by category: "select book where category is")
	 */
	protected abstract void determineTable(ObjectWithID obj, boolean selectObj);
	
	/** returns the real DbColumn of the table (whereas getIDColumn() could
	 *  simply return the ID column by which to search / filter the query) */
	protected abstract DbColumn getAbsoluteIDColumn();
	
	protected abstract DBAccessException foreignKeyConstrainstViolation(String key, Throwable e);
	protected abstract ICrossInsert getCross();
	
	@Override
	protected DBAccessException handleSQLException(SQLException e) {
		if (e.getMessage() != null) {
			String message = e.getMessage();
			if (message.contains("a foreign key constraint fails")) {
				int foreignKey = message.indexOf("FOREIGN KEY (");
				if (foreignKey > 0) {
					int end = message.indexOf(")", foreignKey);
					if (end > 0) {
						return foreignKeyConstrainstViolation(
								message.substring(foreignKey + ("FOREIGN KEY (").length() + 1, end - 1),
								e);
					}
				}
			}
		}
		return new DBAccessException("Unknown error has occured", e);
	}
	
	@Override
	public synchronized boolean exists(String name) throws DBAccessException {
		determineTable(null, false);
		return super.exists(name);
	}
	
	@Override
	public synchronized boolean exists(String name, int id, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		
		AbstractDBTable table = getTable();
		if (table instanceof AbstractCrossDBTable) {
			ResultSet rs = null;
			try {
				rs = DBHandler.executeQuery(
						QueriesEnvironment.selectCrossQuery(
								getNameColumn().getName() + " = ? AND " + getAbsoluteIDColumn().getName() + " = ? ",
							new Object[] { name, id },
							(AbstractCrossDBTable)table, table));
		        rs.first();
		        return (rs.getInt(1) != 0);
			} catch (SQLException e) {
				throw new DBAccessException(e.getMessage(), e);
			} finally {
				try {
					if (rs != null)
						rs.close();
				} catch (SQLException e) {
					throw handleSQLException(e);
				}
			}
		} else {
			return super.exists(name);
		}
	}
	
	@Override
	public synchronized boolean exists(int idToSearch, int id, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		ResultSet rs = null;
		try {
			AbstractDBTable table = getTable();
			if (table instanceof AbstractCrossDBTable) {
				rs = DBHandler.executeQuery(
						QueriesEnvironment.selectCrossQuery(
								getIDColumn().getName() + " = ? AND " + getAbsoluteIDColumn().getName() + " = ? ",
								new Object[] { idToSearch, id },
								(AbstractCrossDBTable)table, table));
				if (!rs.first())
					return false;
		        return true;
			}
			//not cross table -- should not happen for this implementation!:
			return false;
		} catch (SQLException e) {
			throw handleSQLException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
		}
	}
	
	@Override
	public synchronized List<T> select(int id) throws DBAccessException {
		determineTable(null, false);
		return super.select(id);
	}
	
	@Override
	public synchronized List<T> select(int id, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		return super.select(id);
	}

	@Override
	public synchronized List<T> select(String name) throws DBAccessException {
		determineTable(null, false);
		return super.select(name);
	}

	@Override
	public synchronized List<T> selectContains(String name) throws DBAccessException {
		determineTable(null, false);
		return super.selectContains(name, false);
	}

	@Override
	public synchronized List<T> selectContains(String name, boolean limitSearch) throws DBAccessException {
		determineTable(null, false);
		return super.selectContains(name, limitSearch);
	}

	@Override
	public synchronized List<T> select(String name, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		return super.select(name);
	}

	@Override
	public synchronized List<T> selectContains(String name, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		return super.selectContains(name, false);
	}

	@Override
	public synchronized List<T> selectContains(String name, ObjectWithID obj, boolean limitSearch) throws DBAccessException {
		determineTable(obj, true);
		return super.selectContains(name, limitSearch);
	}

	@Override
	public synchronized float selectAvg(int id) throws DBAccessException {
		determineTable(null, false);
		return super.selectAvg(id);
	}

	@Override
	public synchronized int selectCount(int id, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, true);
		return super.selectCount(id, obj);
	}
	
	@Override
	public synchronized List<T> selectAll() throws DBAccessException {
		determineTable(null, false);
		return super.selectAll();
	}
	
	@Override
	public synchronized List<? extends ObjectWithID> selectAll(int id, ObjectWithID obj) throws DBAccessException {
		determineTable(obj, false);

		try {
			AbstractDBTable table = getTable();
			if (table instanceof AbstractCrossDBTable) {
				return DBHandler.convertResultSetToBaseObjectList(
						DBHandler.executeQuery(
								QueriesEnvironment.selectCrossQuery(
										getIDColumn().getName() + " = ? ",
										new Object[] { id },
										(AbstractCrossDBTable)table, ((AbstractCrossDBTable)table).getTable2())),
						((AbstractCrossDBTable)table).getTable2());
			} else {
				return super.select(id);
			}
		} catch (SQLException e) {
			throw handleSQLException(e);
		}
	}
	
	@Override
	public void insert(T obj) throws DBAccessException {
		determineTable(null, false);
		super.insert(obj);
	}
	
	@Override
	public void insert(T obj, ObjectWithID base) throws DBAccessException {
		determineTable(base, false);
		super.insert(obj, base);
	}
	
	@Override
	public void insertAll(Collection<T> objs) throws DBAccessException {
		determineTable(null, false);
		super.insertAll(objs);
	}
	
	@Override
	public void insertAll(T obj, Collection<? extends ObjectWithID> objs) throws DBAccessException {
		determineTable((objs.isEmpty() ? null : objs.iterator().next()), false);
		super.insertAll(obj, objs);
	}
	
	@Override
	public void insertAll(Collection<? extends ObjectWithID> objs,
			ObjectWithID filter) throws DBAccessException {
		determineTable(filter, false);
		try {
			DBHandler.executeUpdateBatch(QueriesEnvironment.buildCrossBatchQuery(
					(AbstractCrossDBTable)getTable(), objs, getCross()));
		} catch (SQLException e) {
			e.printStackTrace();
			throw handleSQLException(e);
		}
	}

	
	@Override
	public void update(T obj) throws DBAccessException {
		determineTable(null, false);
		super.update(obj);
	}
	
	@Override
	public void update(Collection<T> objs) throws DBAccessException {
		determineTable(null, false);
		super.update(objs);
	}
	
	@Override
	public void delete(T obj) throws DBAccessException {
		determineTable(null, false);
		super.delete(obj);
	}

	@Override
	public void delete(Collection<T> objs) throws DBAccessException {
		determineTable(null, false);
		super.delete(objs);
	}
	
	@Override
	public void delete(T obj1, ObjectWithID obj2) throws DBAccessException {
		ArrayList<ObjectWithID> objs = new ArrayList<ObjectWithID>();
		objs.add(obj2);
		delete(obj1, objs);		
	}
	
	@Override
	public void delete(T obj, Collection<? extends ObjectWithID> objs)
			throws DBAccessException {
		determineTable((objs.isEmpty() ? null : objs.iterator().next()), false);
		AbstractDBTable table = getTable();
		if (table instanceof AbstractCrossDBTable) {
			try {
				DBHandler.executeUpdateBatch(
						QueriesEnvironment.doCrossDelete(
								obj, objs, (AbstractCrossDBTable)table));
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
		}
		//otherwise, this method has been wrongly used - do nothing!
 	}
	
}
